﻿using System;
using System.Collections.Generic;
using System.Text;
using Runda.CMS.Common.Utility;
using System.Data;
using System.Data.Common;

namespace Runda.CMS.Dal
{
    public partial class SysDutyDal:DalBase
    {
        public DataTable QueryDuty(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select top 10 * from  (select a.DutyID,a.Date,b.DuctyGroupName from Cms_Sys_Duty a join Cms_Sys_DutyGroup b
on a.DutyGroupID = b.DutyGroupID) as ZZ where datediff(day,date,getdate()) <= 0 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable QueryDutyAll(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select * from  (select a.DutyID,a.Date,b.DuctyGroupName from Cms_Sys_Duty a join Cms_Sys_DutyGroup b
on a.DutyGroupID = b.DutyGroupID) as ZZ where 1=1 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
    }
}
